package com.fpno.preparedStatement.crud;

import com.fpno.bean.Customer;
import com.fpno.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/*
* 针对customers表的查询操作
* */
public class CustomerForQuery {

    @Test
    public void testQueryForCustomers() throws Exception {
        String sql="select id,name,birth,email from customers";
        Customer customer = queryForCustomers(sql);
        System.out.println(customer);
    }

    /*
    * 针对customers表的通用查询操作
    * */
    public Customer queryForCustomers(String sql,Object...args) throws Exception{
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+1,args[i]);
        }
        ResultSet resultSet = ps.executeQuery();
        //获取结果集的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        //通过ResultSetMetaData获取结果集中的列数
        int columnCount = metaData.getColumnCount();
        if (resultSet.next()){
            Customer customer = new Customer();
            //处理结果集一行数据中的每一个列
            for (int i=0;i<columnCount;i++){
                Object columnValue = resultSet.getObject(i + 1);
                //获取每个列的列名
                String columnName = metaData.getColumnName(i+1);
                //给customer对象指定的某个属性赋值为columnValue,通过反射
                Field field = Customer.class.getDeclaredField(columnName);
                /*当类的某个属性或方法的访问权限为private的时候，可以利用setAccessible方法设置
java的访问权限*/
                field.setAccessible(true);
                field.set(customer,columnValue);
            }
            JDBCUtils.closeResource(connection,ps,resultSet);
            return customer;
        }
        JDBCUtils.closeResource(connection,ps,resultSet);
        return null;
    }











    @Test
    public void testQuery1() throws Exception{
        Connection connection = JDBCUtils.getConnection();
        String sql="select id,name,email,birth from customers where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1,1);

        //执行并返回结果集
        ResultSet resultSet = ps.executeQuery();
        //处理结果集
        if(resultSet.next()){
            //判断结果集下一条是否有数据，有则返回true

            //过去当前数据的各个字段值
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.getDate(4);

            //方式一
//            System.out.println("id:"+id+"name:"+name+"email:"+email+"birth:"+birth);

            //方式二
//            Object[] data=new Object[]{id,name,email,birth};

            //方式三 将数据封装为一个对象(推荐)
            Customer customer = new Customer(id,name,email,birth);
            System.out.println(customer);

            //关门资源
            JDBCUtils.closeResource(connection,ps,resultSet);
        }
    }
}
